import time
import datetime
from base_server.sqlop import *


class Bug_Calculate(SqlOperate):
    # 连接数据库
    def __init__(self):
        conf = configparser.ConfigParser()
        conf.read("conf/config.ini")
        self.host = conf.get('qa', 'host')
        self.user = conf.get('qa', 'user')
        self.passwd = conf.get('qa', 'passwd')
        self.database = conf.get('qa', 'database')

    # 插入数据
    def insData(self, tablename, field_item):
        self.dbcur()
        sql = self.sqlInsert(tablename, field_item)
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()

    # 插入质量数据
    def bugInsert(self, vname, proname, versionname, name, checknum, fristnum, leaknum, newnum, bugcount, bugdensity,
                  fristleak, bringerror, addtime):
        data1 = self.selbugcalculate(vname,proname,versionname)
        self.dbcur()
        sql = ""
        if data1:
            sql = "update bugcalculate SET name ='%s',checknum='%s',fristnum='%s',leaknum='%s',newnum='%s',bugcount='%s',bugdensity='%s',fristleak='%s',bringerror='%s',addtime='%s' where vname ='%s'and proname='%s' and versionname='%s'" %(name,checknum,fristnum,leaknum,newnum,bugcount,bugdensity,fristleak,bringerror,addtime,vname,proname,versionname)
        else:
            sql ="INSERT INTO bugcalculate (vname,proname,versionname,name,checknum,fristnum,leaknum,newnum,bugcount,bugdensity,fristleak,bringerror,addtime)VALUES( '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(vname,proname,versionname,name,checknum,fristnum,leaknum,newnum,bugcount,bugdensity,fristleak,bringerror,addtime)
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.bugnewsel()
        data_day = self.bugnewsel_day()
        if data:
            self.bugdel()
        else:
            self.bugnewinser()
            
        if data_day:
            self.bugdel_day()
        else:
            self.bugnewinser_day()
        return "pass"

    #查询新增bug率数据是否存在
    def selbugcalculate(self,vname,proname,versionname):
        self.dbcur()
        sql = "select vname,proname,versionname from bugcalculate where vname ='%s' and proname ='%s' and versionname ='%s' "%(vname,proname,versionname)
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data

    #删除某条bug数据
    # def delbugcalculate(self,vname,proname,versionname):
    #     self.dbcur()
    #     sql = "delete from buproname,gcalculate where vname ='%s'AND proname='%s' AND versionname='%s'" %(vname,proname,versionname)
    #     self.sqlExe(sql)
    #     self.sqlCom()
    #     self.sqlclo()
    #     return "pass"

    #查询bug密度、首轮漏测率、引入错误率
    def getInfor(self,vname,proname,versionname):
        self.dbcur()
        #sql = "select bugdensity,fristleak,bringerror from bugcalculate order by id desc limit 1"
        sql = "select bugdensity,fristleak,bringerror from bugcalculate where vname ='%s'AND proname='%s' AND versionname='%s'" %(vname,proname,versionname)
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data=self.cur.fetchone()
        return data

    # 查询质量数据
    def getInfor1(self):
        self.dbcur()
        sql = "select * from bugcalculate order by addtime desc limit 30"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data

    # 查询bug率数据
    def bugselect(self):
        self.dbcur()
        sql = "select * from bugmidu"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data

    # 判断是否有本月bug率数据
    def bugnewsel(self):
        self.dbcur()
        sql = "select * from bugmidu WHERE addtime= DATE_FORMAT(CURDATE(),'%Y-%m')"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data

    # 新增本月bug率
    def bugnewinser(self):
        self.dbcur()
        sql = "insert into bugmidu(addtime,density,leakage,lead,updatetime)select DATE_FORMAT( addtime, '%Y-%m' ),ROUND(sum(bugdensity)/count(proname),2),ROUND(sum(fristleak)/count(proname),2),ROUND(sum(bringerror)/count(proname),2),CURDATE( ) from bugcalculate WHERE DATE_FORMAT( addtime, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' )  group by DATE_FORMAT(addtime,'%Y-%m')"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        return "pass"

    # 查询本月质量数据
    def buginsert(self):
        self.dbcur()
        sql = "select ROUND(sum(bugdensity)/count(proname),2),ROUND(sum(fristleak)/count(proname),2),ROUND(sum(bringerror)/count(proname),2) from bugcalculate WHERE DATE_FORMAT( addtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )  group by DATE_FORMAT(addtime,'%Y%m')"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchone()
        return data

    # 更新bug率本月数据
    def bugdel(self):
        data = self.buginsert()
        self.dbcur()
        sql = "update bugmidu SET `density`=%.2f, `leakage`=%.2f, `lead`=%.2f,`updatetime`='%s' WHERE addtime='%s'" % (
        data[0], data[1], data[2], time.strftime('%Y-%m-%d', time.localtime(time.time())),
        time.strftime('%Y-%m', time.localtime(time.time())))
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        return "pass"

    # 更新bug率本月数据
    def countbug(self):
        self.dbcur()
        # sql = "select ROUND(sum(bugdensity)/count(proname),2),ROUND(sum(fristleak)/count(proname),2),ROUND(sum(bringerror)/count(proname),2),date_format(CURDATE( ),'%Y-%m-%d') as time from bugcalculate where date_format(addtime,'%Y-%m') < date_format(CURDATE( ),'%Y-%m')"
        sql = " select ROUND(sum(bugdensity)/count(proname),2),ROUND(sum(fristleak)/count(proname),2),ROUND(sum(bringerror)/count(proname),2),left(date_add(now(),interval-1 month),7)as datatime,count(*)as zong from bugcalculate where date_format(addtime,'%Y-%m') < date_format(CURDATE( ),'%Y-%m')"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        l = []
        for i in data:
            l.append(i[0])
            l.append(i[1])
            l.append(i[2])
            l.append(i[3])
            l.append(i[4])
        return l

    # 查询出周数据
    def weekcount(self):
        self.dbcur()
        sql = "SELECT id,vname,proname,versionname,checknum,fristnum,leaknum,newnum,bugcount,bugdensity,fristleak,bringerror,addtime FROM bugcalculate  WHERE YEARWEEK(date_format(addtime,'%Y-%m-%d')) = YEARWEEK(now())"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data

    # 查询重新打开bug数
    def bug_reopen(self):
        self.dbcur()
        sql = "SELECT bug_name,COUNT(*)AS COUNT FROM bug_reopen GROUP BY bug_name"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data

    # 查询reopenx
    def sel_reopen(self,name):
        self.dbcur()
        sql = "SELECT * FROM bug_reopen where bug_name='%s' order by addtime desc"%name
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data1 = self.cur.fetchall()
        return data1


    #以下方法按天计算
    # 查询bug率数据
    def bugselect_day(self):
        self.dbcur()
        sql = "select * from (select * from `bugmidu_day` where WeekDay(updatetime)  between 0 and 4 order by updatetime  DESC limit 60 )  aa ORDER BY id"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data


    # 获取N天前的日期
    def get_end_date(self,beforeOfDay=28):
        """
        :param s_day: 开始时间
        :param beforeOfDay: 向前推的天数
        :return: N天前的日期
        """
        local_data=time.strftime('%Y-%m-%d',time.localtime(time.time()))
        day = datetime.datetime.strptime(local_data,'%Y-%m-%d')
        # 计算偏移量
        offset = datetime.timedelta(days=-beforeOfDay)
        # 获取想要的日期的时间
        n_day = (day + offset).strftime('%Y-%m-%d')
        return n_day

    # 查询当天的数据
    def sql_query(self):
        """
        :param s_day: 开始时间
        :return: 当天-->28天前的日期  这个时间范围内所有的数据
        """
        s_day=time.strftime('%Y-%m-%d',time.localtime(time.time()))
        e_day = self.get_end_date()
        self.dbcur()
        sql = "select id,bugdensity,fristleak,bringerror,addtime from bugcalculate where addtime between '{}' and '{}';".format(e_day,s_day)
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        datas = self.cur.fetchall()
        return datas

    def bug_calc(self):
        """
        计算bug信息
        :param datas:某一天的数据
        :return:bug密度，首轮漏测率，引入错误率
        bug密度=round(sum(bugdensity)/count(proname),2)(保留两位小数)
        首轮漏测率=round(sum(fristleak)/count(proname),2)(保留两位小数)
        引入错误率=round(sum(bringerror)/count(proname),2)(保留两位小数)
        """
        datas=self.sql_query()
        bug_info = {}
        bug_01 = []
        bug_02 = []
        bug_03 = []
        for i in datas:
            bug_01.append(i[1])
            bug_02.append(i[2])
            bug_03.append(i[3])

        bug_01_sum = float(0)
        bug_02_sum = float(0)
        bug_03_sum = float(0)
        for x in bug_01:
            bug_01_sum += float(x)
        for y in bug_02:
            bug_02_sum += float(y)
        for z in bug_03:
            bug_03_sum += float(z)

        bug_info["bugdensity"] = round(bug_01_sum/len(bug_01),2)
        bug_info["fristleak"] = round(bug_02_sum/len(bug_02),2)
        bug_info["bringerror"] = round(bug_03_sum/len(bug_03),2)
        bug_info["addtime"]=time.strftime('%Y-%m-%d',time.localtime(time.time()))
        bug_info["updatetime"] = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        return bug_info


    # 新增本天bug率
    def bugnewinser_day(self):
        qua_data=self.bug_calc()
        self.dbcur()
        sql = "insert into bugmidu_day(addtime,density,leakage,lead,updatetime)VALUES('%s','%s','%s','%s','%s')" %(qua_data['addtime'],qua_data['bugdensity'],qua_data['fristleak'],qua_data['bringerror'],qua_data['updatetime'])
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        return "pass1"

    # 更新bug率本天数据
    def bugdel_day(self):
        data = self.bug_calc()
        self.dbcur()
        sql = "update bugmidu_day SET `density`=%.2f, `leakage`=%.2f, `lead`=%.2f,`updatetime`='%s' WHERE addtime='%s'" % (
        data['bugdensity'], data['fristleak'], data['bringerror'], time.strftime('%Y-%m-%d', time.localtime(time.time())),data['addtime'])
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        return "pass2"

    # 判断是否有本天bug率数据
    def bugnewsel_day(self):
        self.dbcur()
        sql = "SELECT * FROM `bugmidu_day` WHERE addtime= DATE_FORMAT(CURDATE(),'%Y-%m-%d')"
        self.sqlExe(sql)
        self.sqlCom()
        self.sqlclo()
        data = self.cur.fetchall()
        return data
